package com.housekeeping.dao.impl;

import com.housekeeping.dao.ReviewDAO;
import com.housekeeping.model.Review;
import com.housekeeping.utils.DBUtils;

import java.util.List;

/**
 * 评价数据访问实现类
 */
public class ReviewDAOImpl implements ReviewDAO {

    @Override
    public Review findById(Integer reviewId) {
        String sql = "SELECT r.review_id as reviewId, r.order_id as orderId, r.user_id as userId, r.service_id as serviceId, " +
                    "r.staff_id as staffId, r.rating, r.content, r.create_time as createTime, r.update_time as updateTime, " +
                    "u.real_name as userName, s.service_name as serviceName, staff.real_name as staffName, o.order_no as orderNo " +
                    "FROM reviews r " +
                    "LEFT JOIN users u ON r.user_id = u.user_id " +
                    "LEFT JOIN housekeeping_services s ON r.service_id = s.service_id " +
                    "LEFT JOIN users staff ON r.staff_id = staff.user_id " +
                    "LEFT JOIN orders o ON r.order_id = o.order_id " +
                    "WHERE r.review_id = ?";
        return DBUtils.queryForObject(sql, Review.class, reviewId);
    }

    @Override
    public List<Review> findAll() {
        String sql = "SELECT r.review_id as reviewId, r.order_id as orderId, r.user_id as userId, r.service_id as serviceId, " +
                    "r.staff_id as staffId, r.rating, r.content, r.create_time as createTime, r.update_time as updateTime, " +
                    "u.real_name as userName, s.service_name as serviceName, staff.real_name as staffName, o.order_no as orderNo " +
                    "FROM reviews r " +
                    "LEFT JOIN users u ON r.user_id = u.user_id " +
                    "LEFT JOIN housekeeping_services s ON r.service_id = s.service_id " +
                    "LEFT JOIN users staff ON r.staff_id = staff.user_id " +
                    "LEFT JOIN orders o ON r.order_id = o.order_id " +
                    "ORDER BY r.create_time DESC";
        return DBUtils.queryForList(sql, Review.class);
    }

    @Override
    public Review findByOrderId(Integer orderId) {
        String sql = "SELECT r.review_id as reviewId, r.order_id as orderId, r.user_id as userId, r.service_id as serviceId, " +
                    "r.staff_id as staffId, r.rating, r.content, r.create_time as createTime, r.update_time as updateTime, " +
                    "u.real_name as userName, s.service_name as serviceName, staff.real_name as staffName, o.order_no as orderNo " +
                    "FROM reviews r " +
                    "LEFT JOIN users u ON r.user_id = u.user_id " +
                    "LEFT JOIN housekeeping_services s ON r.service_id = s.service_id " +
                    "LEFT JOIN users staff ON r.staff_id = staff.user_id " +
                    "LEFT JOIN orders o ON r.order_id = o.order_id " +
                    "WHERE r.order_id = ?";
        return DBUtils.queryForObject(sql, Review.class, orderId);
    }

    @Override
    public List<Review> findByServiceId(Integer serviceId) {
        String sql = "SELECT r.review_id as reviewId, r.order_id as orderId, r.user_id as userId, r.service_id as serviceId, " +
                    "r.staff_id as staffId, r.rating, r.content, r.create_time as createTime, r.update_time as updateTime, " +
                    "u.real_name as userName, s.service_name as serviceName, staff.real_name as staffName, o.order_no as orderNo " +
                    "FROM reviews r " +
                    "LEFT JOIN users u ON r.user_id = u.user_id " +
                    "LEFT JOIN housekeeping_services s ON r.service_id = s.service_id " +
                    "LEFT JOIN users staff ON r.staff_id = staff.user_id " +
                    "LEFT JOIN orders o ON r.order_id = o.order_id " +
                    "WHERE r.service_id = ? " +
                    "ORDER BY r.create_time DESC";
        return DBUtils.queryForList(sql, Review.class, serviceId);
    }

    @Override
    public List<Review> findByStaffId(Integer staffId) {
        String sql = "SELECT r.review_id as reviewId, r.order_id as orderId, r.user_id as userId, r.service_id as serviceId, " +
                    "r.staff_id as staffId, r.rating, r.content, r.create_time as createTime, r.update_time as updateTime, " +
                    "u.real_name as userName, s.service_name as serviceName, staff.real_name as staffName, o.order_no as orderNo " +
                    "FROM reviews r " +
                    "LEFT JOIN users u ON r.user_id = u.user_id " +
                    "LEFT JOIN housekeeping_services s ON r.service_id = s.service_id " +
                    "LEFT JOIN users staff ON r.staff_id = staff.user_id " +
                    "LEFT JOIN orders o ON r.order_id = o.order_id " +
                    "WHERE r.staff_id = ? " +
                    "ORDER BY r.create_time DESC";
        return DBUtils.queryForList(sql, Review.class, staffId);
    }

    @Override
    public List<Review> findByUserId(Integer userId) {
        String sql = "SELECT r.review_id as reviewId, r.order_id as orderId, r.user_id as userId, r.service_id as serviceId, " +
                    "r.staff_id as staffId, r.rating, r.content, r.create_time as createTime, r.update_time as updateTime, " +
                    "u.real_name as userName, s.service_name as serviceName, staff.real_name as staffName, o.order_no as orderNo " +
                    "FROM reviews r " +
                    "LEFT JOIN users u ON r.user_id = u.user_id " +
                    "LEFT JOIN housekeeping_services s ON r.service_id = s.service_id " +
                    "LEFT JOIN users staff ON r.staff_id = staff.user_id " +
                    "LEFT JOIN orders o ON r.order_id = o.order_id " +
                    "WHERE r.user_id = ? " +
                    "ORDER BY r.create_time DESC";
        return DBUtils.queryForList(sql, Review.class, userId);
    }

    @Override
    public List<Review> findByRating(Integer rating) {
        String sql = "SELECT r.review_id as reviewId, r.order_id as orderId, r.user_id as userId, r.service_id as serviceId, " +
                    "r.staff_id as staffId, r.rating, r.content, r.create_time as createTime, r.update_time as updateTime, " +
                    "u.real_name as userName, s.service_name as serviceName, staff.real_name as staffName, o.order_no as orderNo " +
                    "FROM reviews r " +
                    "LEFT JOIN users u ON r.user_id = u.user_id " +
                    "LEFT JOIN housekeeping_services s ON r.service_id = s.service_id " +
                    "LEFT JOIN users staff ON r.staff_id = staff.user_id " +
                    "LEFT JOIN orders o ON r.order_id = o.order_id " +
                    "WHERE r.rating = ? " +
                    "ORDER BY r.create_time DESC";
        return DBUtils.queryForList(sql, Review.class, rating);
    }

    @Override
    public Double getAverageRatingByServiceId(Integer serviceId) {
        String sql = "SELECT AVG(rating) FROM reviews WHERE service_id = ?";
        Object result = DBUtils.queryForScalar(sql, serviceId);
        return result != null ? ((Number) result).doubleValue() : 0.0;
    }

    @Override
    public Double getAverageRatingByStaffId(Integer staffId) {
        String sql = "SELECT AVG(rating) FROM reviews WHERE staff_id = ?";
        Object result = DBUtils.queryForScalar(sql, staffId);
        return result != null ? ((Number) result).doubleValue() : 0.0;
    }

    @Override
    public long countByServiceId(Integer serviceId) {
        String sql = "SELECT COUNT(*) FROM reviews WHERE service_id = ?";
        Object result = DBUtils.queryForScalar(sql, serviceId);
        return result != null ? ((Number) result).longValue() : 0L;
    }

    @Override
    public long countByStaffId(Integer staffId) {
        String sql = "SELECT COUNT(*) FROM reviews WHERE staff_id = ?";
        Object result = DBUtils.queryForScalar(sql, staffId);
        return result != null ? ((Number) result).longValue() : 0L;
    }

    @Override
    public int insert(Review review) {
        String sql = "INSERT INTO reviews (order_id, user_id, service_id, staff_id, rating, content) " +
                    "VALUES (?, ?, ?, ?, ?, ?)";
        return DBUtils.update(sql, review.getOrderId(), review.getUserId(), review.getServiceId(),
                review.getStaffId(), review.getRating(), review.getContent());
    }

    @Override
    public int update(Review review) {
        String sql = "UPDATE reviews SET rating = ?, content = ?, update_time = NOW() " +
                    "WHERE review_id = ?";
        return DBUtils.update(sql, review.getRating(), review.getContent(), review.getReviewId());
    }

    @Override
    public int delete(Integer reviewId) {
        String sql = "DELETE FROM reviews WHERE review_id = ?";
        return DBUtils.update(sql, reviewId);
    }

    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM reviews";
        Object result = DBUtils.queryForScalar(sql);
        return result != null ? ((Number) result).longValue() : 0L;
    }

    @Override
    public boolean isOrderReviewed(Integer orderId) {
        String sql = "SELECT COUNT(*) FROM reviews WHERE order_id = ?";
        Object result = DBUtils.queryForScalar(sql, orderId);
        long count = result != null ? ((Number) result).longValue() : 0L;
        return count > 0;
    }
} 